﻿Imports System.Data.OleDb
Module XL_PhieuChi
    Structure PhieuChi
        Private _iD As Integer
        Property ID() As Integer
            Get
                Return _iD
            End Get
            Set(ByVal value As Integer)
                _iD = value
            End Set
        End Property
        Private _ngay As Date
        Property Ngay() As Date
            Get
                Return _ngay
            End Get
            Set(ByVal value As Date)
                _ngay = value
            End Set
        End Property
        Private _soTien As Integer
        Property SoTien() As Integer
            Get
                Return _soTien
            End Get
            Set(ByVal value As Integer)
                _soTien = value
            End Set
        End Property
        Private _loaiChi As Integer
        Property LoaiChi() As Integer
            Get
                Return _loaiChi
            End Get
            Set(ByVal value As Integer)
                _loaiChi = value
            End Set
        End Property
        Private _taiKhoan As Integer
        Property TaiKhoan() As Integer
            Get
                Return _taiKhoan
            End Get
            Set(ByVal value As Integer)
                _taiKhoan = value
            End Set
        End Property
        Private _nguoiChi As Integer
        Property NguoiChi() As Integer
            Get
                Return _nguoiChi
            End Get
            Set(ByVal value As Integer)
                _nguoiChi = value
            End Set
        End Property
        Private _nguoiLap As Integer
        Property NguoiLap() As Integer
            Get
                Return _nguoiLap
            End Get
            Set(ByVal value As Integer)
                _nguoiLap = value
            End Set
        End Property
        Private _chiTiet As String
        Property ChiTiet() As String
            Get
                Return _chiTiet
            End Get
            Set(ByVal value As String)
                _chiTiet = value
            End Set
        End Property
    End Structure

    Public Function LayDS(ByVal sotien1 As Integer, ByVal sotien2 As Integer, ByVal loai As Integer, ByVal tk As Integer, ByVal ngthu As Integer, ByVal nglap As Integer, ByVal thang As Integer, ByVal nam As Integer, ByVal ngay As Date) As List(Of PhieuChi)
        Try
            Dim ds As New List(Of PhieuChi)
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim dk As String
            If sotien1 = -1 And sotien2 = -1 Then
                dk = "where SoTien >= 0"
            ElseIf sotien1 > 0 And sotien2 = -1 Then
                dk = "where SoTien >= " + sotien1.ToString()
            ElseIf sotien1 = -1 And sotien2 > 0 Then
                dk = "where SoTien <= " + sotien2.ToString()
            Else
                dk = "where SoTien >= " + sotien1.ToString() + " and SoTien <=" + sotien2.ToString()
            End If
            If loai > 0 Then
                dk += " and LoaiChi = " + loai.ToString()
            End If
            If tk > 0 Then
                dk += " and TaiKhoan = " + tk.ToString()
            End If
            If ngthu > 0 Then
                dk += " and NguoiChi =" + ngthu.ToString()
            End If
            If nglap > 0 Then
                dk += " and NguoiLap = " + nglap.ToString()
            End If
            If thang > 0 Then
                dk += " and Month(Ngay) = " + thang.ToString()
            End If
            If nam > 0 Then
                dk += " and Year(Ngay) = " + nam.ToString()
            End If
            If ngay > Date.MinValue Then
                dk += " and Ngay = '" + ngay.ToShortDateString() + "'"
            End If
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from PhieuChi " + dk, con)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As PhieuChi = New PhieuChi()
                p.ID = rd.GetInt32(0)
                p.Ngay = rd.GetDateTime(1)
                p.SoTien = rd.GetInt32(2)
                p.LoaiChi = rd.GetInt32(3)
                p.TaiKhoan = rd.GetInt32(4)
                p.NguoiChi = rd.GetInt32(5)
                p.NguoiLap = rd.GetInt32(6)
                p.ChiTiet = rd.GetString(7)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function LayDS2(ByVal sotien1 As Integer, ByVal sotien2 As Integer, ByVal loai As Integer, ByVal tk As Integer, ByVal ngchi As Integer, ByVal nglap As Integer, ByVal thang As Integer, ByVal nam As Integer, ByVal ngay As Date) As DataTable
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim dk As String
            If sotien1 = -1 And sotien2 = -1 Then
                dk = "where SoTien >= 0"
            ElseIf sotien1 > 0 And sotien2 = -1 Then
                dk = "where SoTien >= " + sotien1.ToString()
            ElseIf sotien1 = -1 And sotien2 > 0 Then
                dk = "where SoTien <= " + sotien2.ToString()
            Else
                dk = "where SoTien >= " + sotien1.ToString() + " and SoTien <=" + sotien2.ToString()
            End If
            If loai > 0 Then
                dk += " and LoaiThu = " + loai.ToString()
            End If
            If tk > 0 Then
                dk += " and TaiKhoan = " + tk.ToString()
            End If
            If ngchi > 0 Then
                dk += " and NguoiThu =" + ngchi.ToString()
            End If
            If nglap > 0 Then
                dk += " and NguoiLap = " + nglap.ToString()
            End If
            If thang > 0 Then
                dk += " and Month(Ngay) = " + thang.ToString()
            End If
            If nam > 0 Then
                dk += " and Year(Ngay) = " + nam.ToString()
            End If
            If ngay > Date.MinValue Then
                dk += " and Ngay = '" + ngay.ToShortDateString() + "'"
            End If
            Dim cmd As OleDbCommand = New OleDbCommand("Select p.Ngay, p.SoTien, l.Loai, k.TenTK, u1.TenDN as NguoiChi, u2.TenDN as NguoiLap, p.ChiTiet from PhieuChi p, LoaiChi l, NguoiDung u1, NguoiDung u2, TaiKhoan k " + dk + " and p.LoaiChi=l.ID and p.NguoiChi=u1.ID and p.NguoiLap=u2.ID and p.TaiKhoan=k.ID ", con)
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
            da.SelectCommand = cmd
            Dim dt As DataTable = New DataTable()
            da.Fill(dt)
            con.Close()
            Return (dt)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Lay(ByVal id As Integer) As PhieuChi
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from PhieuChi where ID=@ID", con)
            Dim para As OleDbParameter = New OleDbParameter("@ID", id)
            cmd.Parameters.Add(para)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As PhieuChi = New PhieuChi()
            If rd.Read() Then
                p.ID = rd.GetInt32(0)
                p.Ngay = rd.GetDateTime(1)
                p.SoTien = rd.GetInt32(2)
                p.LoaiChi = rd.GetInt32(3)
                p.TaiKhoan = rd.GetInt32(4)
                p.NguoiChi = rd.GetInt32(5)
                p.NguoiLap = rd.GetInt32(6)
                p.ChiTiet = rd.GetString(7)
            End If
            con.Close()
            Return p
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Them(ByRef p As PhieuChi) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("insert into PhieuChi(Ngay,SoTien,LoaiChi,TaiKhoan, NguoiChi,NguoiLap,ChiTiet) values(@Ngay,@SoTien,@LoaiChi,@TaiKhoan, @NguoiChi,@NguoiLap,@ChiTiet)", con)
            cmd.Parameters.AddWithValue("@Ngay", p.Ngay)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@LoaiChi", p.LoaiChi)
            cmd.Parameters.AddWithValue("@TaiKhoan", p.TaiKhoan)
            cmd.Parameters.AddWithValue("@NguoiChi", p.NguoiChi)
            cmd.Parameters.AddWithValue("@NguoiLap", p.NguoiLap)
            cmd.Parameters.AddWithValue("@ChiTiet", p.ChiTiet)
            Dim kq = cmd.ExecuteNonQuery()
            cmd.CommandText = "select @@Identity"
            p.ID = cmd.ExecuteScalar()

            If kq = 1 Then
                Dim cmd1 As OleDbCommand = New OleDbCommand("select SoDu from TaiKhoan where NguoiDung=@nd and ID=@tk", con)
                cmd1.Parameters.AddWithValue("@nd", p.NguoiChi)
                cmd1.Parameters.AddWithValue("@tk", p.TaiKhoan)
                Dim sd As Integer = cmd1.ExecuteScalar()

                Dim cmd2 As OleDbCommand = New OleDbCommand("update TaiKhoan set SoDu=@sd1-@sd2 where NguoiDung=@nd and ID=@tk", con)
                cmd2.Parameters.AddWithValue("@sd1", sd)
                cmd2.Parameters.AddWithValue("@sd2", p.SoTien)
                cmd2.Parameters.AddWithValue("@nd", p.NguoiChi)
                cmd2.Parameters.AddWithValue("@tk", p.TaiKhoan)
                Dim ud = cmd2.ExecuteNonQuery()
            End If
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Xoa(ByVal p As PhieuChi) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("delete from PhieuChi where ID = @ID", con)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            If kq = 1 Then
                Dim cmd1 As OleDbCommand = New OleDbCommand("select SoDu from TaiKhoan where NguoiDung=@nd and ID=@tk", con)
                cmd1.Parameters.AddWithValue("@nd", p.NguoiChi)
                cmd1.Parameters.AddWithValue("@tk", p.TaiKhoan)
                Dim sd As Integer = cmd.ExecuteScalar()

                Dim cmd2 As OleDbCommand = New OleDbCommand("update TaiKhoan set SoDu=@sd1+@sd2 where NguoiDung=@nd and ID=@tk", con)
                cmd2.Parameters.AddWithValue("@sd1", sd)
                cmd2.Parameters.AddWithValue("@sd2", p.SoTien)
                cmd2.Parameters.AddWithValue("@nd", p.NguoiChi)
                cmd2.Parameters.AddWithValue("@tk", p.TaiKhoan)
                Dim ud = cmd2.ExecuteNonQuery()
            End If
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function CapNhat(ByVal p As PhieuChi) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()

            Dim cm As OleDbCommand = New OleDbCommand("select SoTien from PhieuChi where ID=@id", con)
            cm.Parameters.AddWithValue("@id", p.ID)
            Dim st As Integer = cm.ExecuteNonQuery()

            Dim cmd As OleDbCommand = New OleDbCommand("update PhieuChi set Ngay=@Ngay,SoTien=@SoTien,LoaiChi=@LoaiChi,TaiKhoan=@TaiKhoan, NguoiChi=@NguoiChi,NguoiLap=@NguoiLap,ChiTiet=@ChiTiet where ID = @ID", con)
            cmd.Parameters.AddWithValue("@Ngay", p.Ngay)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@LoaiChi", p.LoaiChi)
            cmd.Parameters.AddWithValue("@TaiKhoan", p.TaiKhoan)
            cmd.Parameters.AddWithValue("@NguoiChi", p.NguoiChi)
            cmd.Parameters.AddWithValue("@NguoiLap", p.NguoiLap)
            cmd.Parameters.AddWithValue("@ChiTiet", p.ChiTiet)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()

            If st <> p.SoTien And kq = 1 Then
                Dim cmd1 As OleDbCommand = New OleDbCommand("select SoDu from TaiKhoan where NguoiDung=@nd and ID=@tk", con)
                cmd1.Parameters.AddWithValue("@nd", p.NguoiChi)
                cmd1.Parameters.AddWithValue("@tk", p.TaiKhoan)
                Dim sd As Integer = cmd.ExecuteScalar()

                Dim cmd2 As OleDbCommand = New OleDbCommand("update TaiKhoan set SoDu=@sd1-@st+@sd2 where NguoiDung=@nd and ID=@tk", con)
                cmd2.Parameters.AddWithValue("@sd1", sd)
                cmd2.Parameters.AddWithValue("@st", st)
                cmd2.Parameters.AddWithValue("@sd2", p.SoTien)
                cmd2.Parameters.AddWithValue("@nd", p.NguoiChi)
                cmd2.Parameters.AddWithValue("@tk", p.TaiKhoan)
                Dim ud = cmd2.ExecuteNonQuery()
            End If
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function
End Module
